import requests
import pymysql
from lxml import etree

url = 'https://www.baidu.com/?tn=sitehao123_15'
headers = {
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8',
    'Accept-Encoding': 'gzip, deflate, sdch',
    'Accept-Language': 'zh-CN,zh;q=0.8',
    'Cache-Control': 'max-age=0',
    'Connection': 'keep-alive',
    'Cookie': 'BAIDUID=BF261DF7BE608F8AEC27CFB66925E8C2:FG=1; BIDUPSID=BF261DF7BE608F8AEC27CFB66925E8C2; PSTM=1653268040; BDRCVFR[PGnakqNNAQT]=9xWipS8B-FspA7EnHc1QhPEUf; BD_HOME=1; H_PS_PSSID=36462_36455_31254_34813_35911_36165_35978_36055_36419_26350_36299_36469_36314_36447; BAIDUID_BFESS=BF261DF7BE608F8AEC27CFB66925E8C2:FG=1; BA_HECTOR=al8h008gala5a52k2g1h8lnj014; ZFY=w7b6sFbp:B7w83vprDGxHW8VDl2VCjC4TkFAzEmr63WQ:C',
    'Host': 'www.baidu.com',
    'Referer': 'http://www.hao123.com/?tn=92027909_hao_pg',
    'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.86 Safari/537.36'
}
rq = requests.get(url, headers=headers)
html = rq.content.decode('utf-8')

# 把字符串转换为网页
result = etree.HTML(html)

# 使用xpath来解析网页，找到我们需要找的字段
hotsearchs =result.xpath('//span[@class=\'title-content-title\']/text()')


# 创建数据库连接
db = pymysql.connect(host='127.0.0.1',
                     port=3306,
                     user='root',
                     passwd='123456',
                     db='stu',
                     charset='utf8mb4',
                     connect_timeout=1000)
# 创建游标
cursor = db.cursor()
# 创建表
sql = """create table if not exists class (id int(10) primary key auto_increment,name varchar(20) not null,text varchar(20) not null)"""
cursor.execute(sql)
cursor.execute("show tables")
for tupleitem in cursor.fetchall():
    print(tupleitem[0])

# 插入热搜数据到数据库里
index = 1
for i in hotsearchs:
    cursor.execute("insert into class(name,text) values(%s,%s)", ['榜'+str(index), i])
    index += 1
db.commit()
